<?php
//admin.php  written by Ben Quartermaine June 2009
//Purpose: *View of subscribers database with query functions

include "SuperHTMLDef.php";

// Connect to Database
$conn = mysql_connect("localhost", "root", "BlackBall56") or die (mysql_error());
mysql_select_db("emailSubs", $conn);

checkCookie();

//close database connection
mysql_close($conn);	 


function checkCookie()
{
	global $conn;
    //checks cookies to make sure they are logged in
    if ( isset ($_COOKIE['ID_my_site']))
    {
        $username = $_COOKIE['ID_my_site'];
        $pass = $_COOKIE['Key_my_site'];
        $check = mysql_query("SELECT * FROM users WHERE username = '$username'", $conn) or die (mysql_error());
        while ($info = mysql_fetch_array($check))
        {

            //if the cookie has the wrong password, they are taken to the login page
            if ($pass != $info['password'])
            {
                header("Location: login.php");
            }
            //otherwise they are shown the admin area
            else
            {
                showForm();
            }
        }
    } else

    //if the cookie does not exist, they are taken to the login screen
    {
        print "You are not logged in please log in here: <a href='./login.php'>Login Screen</a>";
    }

}



function showForm()
{
    //get form variables
    if (filter_has_var(INPUT_POST, "filter"))
    {
        $filter = filter_input(INPUT_POST, "filter");
        switch($filter)
        {
            case 0:
                $heading = "(Full List)";
                break;
            case 1:
                $heading = "who are currently Opted In";
                break;
            case 2:
                $heading = "who are currently Opted Out";
                break;
            case 3:
                $heading = "who Joined Today";
                break;
            case 4:
                $heading = "who Joined this Week";
                break;
        }//end switch

        $title = "<h3>Subscriber database</h3> View of subscribers $heading";

    }
    else
    {
        $filter = "0";
        $title = "Default Subscriber database view";
    } //end if else

    if (filter_has_var(INPUT_POST, emailFilter))
    {
        $emailFilter = filter_input(INPUT_POST, emailFilter);
    } else
    {
        $emailFilter = "";
    }

    $s = new SuperHTML("Subsciber administration");

    //main body
    $s->buildTop();

    $s->tag("p", $title);

    $ol1 = "<ol>";
    $ol2 = "</ol>";
    $lt1 = "<li>";
    $lt2 = "</li><li>";
    $lt3 = "</li>";

    $s->startForm("admin.php", "post", "DBQuery");
    $selVals = array ("Full List", "Opted In", "Opted Out", "Joined Today", "Joined This Week");
    $s->addText($ol1);
    $s->addText($lt1);
    $s->label("Filter");
    $s->select("filter", $selVals);
    $s->addText($lt2);
    $s->label("Email Search");
    $s->textbox("emailFilter", "$emailFilter");
    $s->addText($lt3);
    $s->submit();
    $s->addText($ol2);
    $table = printTable($filter, $emailFilter);
    $s->addText("<br />");
    $s->addText($table);
    $s->endForm();
	$s->addText("<a href='./logout.php'>Logout</a>");

    //print $s->formResults();

    $s->buildBottom();
    print $s->getPage();
}


function printTable($filter, $emailFilter)
{
	global $conn;
    //create a query
    switch($filter)
    {
        case 0:
            if ($emailFilter != "")
            {
                $sql = "SELECT * FROM subscribers WHERE email LIKE '%$emailFilter%';";
            } else
            {
                $sql = "SELECT * FROM subscribers";
            }
        break;

        case 1:
            if ($emailFilter != "")
            {
                $sql = "SELECT * FROM subscribers WHERE status = 'in' AND email LIKE '%$emailFilter%';";
            } else
            {
                $sql = "SELECT * FROM subscribers WHERE status = 'in';";
            }
        break;

        case 2:
            if ($emailFilter != "")
            {
                $sql = "SELECT * FROM subscribers WHERE status = 'out' AND email LIKE '%$emailFilter%';";
            } else
            {
                $sql = "SELECT * FROM subscribers WHERE status = 'out';";
            }
        break;

        case 3:
            if ($emailFilter != "")
            {
                $sql = "SELECT * FROM subscribers WHERE DATEDIFF(NOW(),dateJoined) <= 1 AND email LIKE '%$emailFilter%';";
            } else
            {
                $sql = "SELECT * FROM subscribers WHERE DATEDIFF(NOW(),dateJoined) <= 1;";
            }
        break;

        case 4:
            if ($emailFilter != "")
            {
                $sql = "SELECT * FROM subscribers WHERE DATEDIFF(NOW(),dateJoined) <= 7 AND email LIKE '%$emailFilter%';";
            } else
            {
                $sql = "SELECT * FROM subscribers WHERE DATEDIFF(NOW(),dateJoined) <= 7;";
            }
        break;
}//end switch

//print $sql;
//print $emailFilter;

$result = mysql_query($sql, $conn) or die (mysql_error);

$tableText = "";
$tableText = "<table border = \"1\">\n";

//get field names
$tableText .= "<tr>\n";
//print "<tr>\n";
while ($field = mysql_fetch_field($result))
{
    $tableText .= "  <th>$field->name</th>\n";
} // end while
$tableText .= "</tr>\n\n";

//get row data as an associative array
while ($row = mysql_fetch_assoc($result))
{
    $tableText .= "<tr>\n";
    //look at each field
    foreach ($row as $col=>$val)
    {
        $tableText .= "  <td>$val</td>\n";
    } // end foreach
    $tableText .= "</tr>\n\n";
}// end while

$tableText .= "</table>\n";

return $tableText;
}





?>
